//construct CZ employment shocks
//2000-2009
forval i = 0/9{
	import excel using "$data/Other/bls/laucnty0`i'.xlsx", clear
	keep B C E J
	gen fips = B + C
	ren E year
	ren J unemp_rate
	drop B C 
	destring year fips unemp, replace force
	drop if year == .
	drop year
	ren unemp unemp_rate_200`i'
	duplicates tag fips, gen(dup)
	drop if dup
	drop dup
	save "$temp/county_unemp_200`i'", replace	
}

forval i = 10/18{
	import excel using "$data/Other/bls/laucnty`i'.xlsx", clear
	keep B C E J
	gen fips = B + C
	ren E year
	ren J unemp_rate
	drop B C 
	destring year fips unemp, replace force
	drop if year == .
	drop year
	ren unemp unemp_rate_20`i'
	duplicates tag fips, gen(dup)
	drop if dup
	drop dup
	save "$temp/county_unemp_20`i'", replace	
}

//read in CZ-county croswalk
import excel using "$data/Other/cz_xwalk/cz00_eqv_v1.xls", clear 
drop if _n == 1
keep A B G
ren A fips
ren B cz
ren G pop
destring *, replace

preserve
drop pop
save "$data/Other/cz_xwalk/cz_xwalk_small", replace
restore

//merge on unemployment rates
forval y = 0/18{
	local year = 2000 + `y'
	merge 1:1 fips using "$temp/county_unemp_`year'", keep(match) nogen	
	
}
collapse (mean) unemp* [fweight = pop], by(cz)
preserve
gen cz_shock = unemp_rate_2009 - unemp_rate_2007
su cz_shock
keep cz*
save "$temp/cz_shocks", replace
restore
save "$temp/cz_unemps", replace

//create CZ shock data and save
reshape long unemp_rate_, i(cz) j(year)
ren unemp_rate_ unemp_rate
save "$temp/cz_unemps_long", replace

//
